package com.bms.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.bms.entity.Author;

public class AuthorDao {

	/**
	 * 添加作者信息
	 * 
	 * @param author
	 */
	public void insert(Author author) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			String sql = "insert into authors(firstName,lastName) value(?,?)";
			statement = con.prepareStatement(sql);
			// 绑定数据
			statement.setString(1, author.getFirstName());
			statement.setString(2, author.getLastName());

			statement.execute();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 更新作者信息
	 * 
	 * @param author
	 */
	public void update(Author author) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			String sql = "update authors set firstName=?,lastName=? where id=?";
			statement = con.prepareStatement(sql);
			// 绑定数据
			statement.setString(1, author.getFirstName());
			statement.setString(2, author.getLastName());
			statement.setInt(3, author.getId());

			statement.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 根据id删除作者信息
	 * 
	 * @param id
	 */
	public void deleteAuthorById(int id) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			String sql = "delete from authors where id=?";
			statement = con.prepareStatement(sql);
			// 绑定数据
			statement.setInt(1, id);

			statement.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 查询所有的作者信息
	 * 
	 * @return
	 */
	public List<Author> queryAll() {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		Statement statement = null;
		ResultSet resultSet = null;

		List<Author> authors = new ArrayList<Author>();
		Author author = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			statement = con.createStatement();
			String sql = "select * from authors";
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String firstName = resultSet.getString("firstName");
				String lastName = resultSet.getString("lastName");

				author = new Author(id, firstName, lastName);
				authors.add(author);
				author = null;
			}
			return authors;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return null;
	}

	/**
	 * 根据ISBN查询作者信息
	 * 
	 * @param sql查询的sql语句
	 * @param isbn
	 * @return
	 */
	public List<Author> queryAll(String sql, String isbn) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		List<Author> authors = new ArrayList<Author>();
		Author author = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			statement = con.prepareStatement(sql);
			// select * from authors where id not in(
			// select authorId from author_book where isbn=?)
			statement.setString(1, isbn);

			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String firstName = resultSet.getString("firstName");
				String lastName = resultSet.getString("lastName");

				author = new Author(id, firstName, lastName);
				authors.add(author);
				author = null;
			}
			return authors;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return null;
	}

	/**
	 * 根据id查询 作者的信息
	 * 
	 * @param param
	 * @return
	 */
	public Author getAuthorInfo(int param) {
		String url = "jdbc:mysql://localhost:3306/books";// 数据库地址
		String user = "fangzuchang";// 数据库登录名
		String password = "13920778329";
		Connection con = null;// 数据库链接对象
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		Author author = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection(url, user, password);
			String sql = "select * from authors where id=?";
			statement = con.prepareStatement(sql);
			// 绑定数据
			statement.setInt(1, param);

			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String firstName = resultSet.getString("firstName");
				String lastName = resultSet.getString("lastName");

				author = new Author(id, firstName, lastName);
			}
			return author;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return null;
	}
}
